# This file is used to create a relation between user and role in the database.

'''

-- 用户角色关联表    
CREATE TABLE user_role_relation (    
    user_id INTEGER NOT NULL REFERENCES user_info(user_id),    
    role_id INTEGER NOT NULL REFERENCES user_role(role_id),    
    PRIMARY KEY (user_id, role_id)    
);

'''

class UserRoleRelationTableMethod:

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    

    def insert_user_role_relation(self, user_id, role_id):
        '''
        插入用户角色关联表    
        :param user_id: 用户ID    
        :param role_id: 角色ID    
        :return:    
        '''
        sql = "INSERT INTO user_role_relation (user_id, role_id) VALUES (%s, %s)"
        params = (user_id, role_id)
        self.cursor.execute(sql, params)
        self.conn.commit()

    def delete_user_role_relation(self, user_id, role_id):
        '''
        删除用户角色关联表    
        :param user_id: 用户ID    
        :param role_id: 角色ID    
        :return:    
        '''
        sql = "DELETE FROM user_role_relation WHERE user_id = %s AND role_id = %s"
        params = (user_id, role_id)
        self.cursor.execute(sql, params)
        self.conn.commit()

    def select_user_role_relation(self, user_id):
        '''
        查询用户角色关联表    
        :param user_id: 用户ID    
        :return: 角色ID列表    
        '''
        sql = "SELECT role_id FROM user_role_relation WHERE user_id = %s"
        params = (user_id,)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return [i["role_id"] for i in result]
    
    def select_user_role_relation_by_role_id(self, role_id):
        '''
        查询用户角色关联表    
        :param role_id: 角色ID    
        :return: 用户ID列表    
        '''
        sql = "SELECT user_id FROM user_role_relation WHERE role_id = %s"
        params = (role_id,)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        user_id_list = []
        for row in result:
            user_id_list.append(row[0])
        return user_id_list
    

    
    def select_user_role_relation_by_user_id_and_role_id(self, user_id, role_id):    
        '''    
        查询用户角色关联表    
        :param user_id: 用户ID    
        :param role_id: 角色ID    
        :return: True/False    
        '''    
        sql = "SELECT * FROM user_role_relation WHERE user_id = %s AND role_id = %s"    
        params = (user_id, role_id)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchone()    
        if result:    
            return True    
        else:    
            return False    
        
    
    def select_user_role_relation_by_user_id_and_role_name(self, user_id, role_name):    
        '''    
        查询用户角色关联表    
        :param user_id: 用户ID    
        :param role_name: 角色名称    
        :return: 角色ID    
        '''    
        sql = "SELECT role_id FROM user_role_relation, user_role WHERE user_role_relation.user_id = %s AND user_role_relation.role_id = user_role.role_id AND user_role.role_name = %s"    
        params = (user_id, role_name)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchone()    
        if result:    
            return result[0]    
        else:    
            return None    
        
    
    def select_user_role_relation_by_role_name_and_user_id(self, role_name, user_id):    
        '''    
        查询用户角色关联表    
        :param role_name: 角色名称    
        :param user_id: 用户ID    
        :return: 用户ID    
        '''    
        sql = "SELECT user_id FROM user_role_relation, user_role WHERE user_role_relation.role_id = user_role.role_id AND user_role.role_name = %s AND user_role_relation.user_id = %s"    
        params = (role_name, user_id)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchone()    
        if result:    
            return result[0]    
        else:    
            return None    
        
    
    def select_user_role_relation_by_user_name_and_role_name(self, user_name, role_name):    
        '''    
        查询用户角色关联表    
        :param user_name: 用户名称    
        :param role_name: 角色名称    
        :return: 用户ID/None    
        '''    
        sql = "SELECT user_id FROM user_info, user_role_relation, user_role WHERE user_info.user_name = %s AND user_info.user_id = user_role_relation.user_id AND user_role_relation.role_id = user_role.role_id AND user_role.role_name = %s"    
        params = (user_name, role_name)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchone()    
        if result:    
            return result[0]    
        else:    
            return None    
        
    
    def select_user_role_relation_by_user_name_and_role_id(self, user_name, role_id):    
        '''    
        查询用户角色关联表    
        :param user_name: 用户名称    
        :param role_id: 角色ID    
        :return: 用户ID/None    
        '''    
        sql = "SELECT user_id FROM user_info, user_role_relation WHERE user_info.user_name = %s AND user_info.user_id = user_role_relation.user_id AND user_role_relation.role_id = %s"    
        params = (user_name, role_id)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchone()    
        if result:    
            return result[0]    
        else:    
            return None    
    
    def select_user_role_relation_by_role_name_and_user_name(self, role_name, user_name):    
        '''    
        查询用户角色关联表    
        :param role_name: 角色名称    
        :param user_name: 用户名称    
        :return: 用户ID/None    
        '''    
        sql = "SELECT user_id FROM user_info, user_role_relation, user_role WHERE user_info.user_name = %s AND user_info.user_id = user_role_relation.user_id AND user_role_relation.role_id = user_role.role_id AND user_role.role_name = %s"    
        params = (user_name, role_name)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchone()    
        if result:    
            return result[0]    
        else:    
            return None    
        
    
    def select_user_role_relation_by_user_id_and_role_name_list(self, user_id, role_name_list):    
        '''    
        查询用户角色关联表    
        :param user_id: 用户ID    
        :param role_name_list: 角色名称列表    
        :return: 角色ID列表    
        '''    
        sql = "SELECT role_id FROM user_role_relation, user_role WHERE user_role_relation.user_id = %s AND user_role_relation.role_id = user_role.role_id AND user_role.role_name IN %s"    
        params = (user_id, tuple(role_name_list))    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        role_id_list = []    
        for row in result:        
            role_id_list.append(row[0])    
        return role_id_list    
        
    
    def select_user_role_relation_by_user_name_and_role_name_list(self, user_name, role_name_list):    
        '''    
        查询用户角色关联表    
        :param user_name: 用户名称    
        :param role_name_list: 角色名称列表    
        :return: 用户ID列表    
        '''    
        sql = "SELECT user_id FROM user_info, user_role_relation, user_role WHERE user_info.user_name = %s AND user_info.user_id = user_role_relation.user_id AND user_role_relation.role_id = user_role.role_id AND user_role.role_name IN %s"    
        params = (user_name, tuple(role_name_list))    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        user_id_list = []    
        for row in result:        
            user_id_list.append(row[0])    
        return user_id_list    
        
    
    def select_user_role_relation_by_role_name_and_user_name_list(self, role_name, user_name_list):    
        '''    
        查询用户角色关联表    
        :param role_name: 角色名称    
        :param user_name_list: 用户名称列表    
        :return: 用户ID列表    
        '''    
        sql = "SELECT user_id FROM user_info, user_role_relation, user_role WHERE user_info.user_name IN %s AND user_info.user_id = user_role_relation.user_id AND user_role_relation.role_id = user_role.role_id AND user_role.role_name = %s"    
        params = (tuple(user_name_list), role_name)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        user_id_list = []    
        for row in result:        
            user_id_list.append(row[0])    
        return user_id_list    
        
    
    def select_user_role_relation_by_user_id_and_role_id_list(self, user_id, role_id_list):    
        '''    
        查询用户角色关联表    
        :param user_id: 用户ID    
        :param role_id_list: 角色ID列表    
        :return: 角色ID列表    
        '''    
        sql = "SELECT role_id FROM user_role_relation WHERE user_id = %s AND role_id IN %s"    
        params = (user_id, tuple(role_id_list))    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        role_id_list = []    
        for row in result:        
            role_id_list.append(row[0])    
        return role_id_list    
        
    
    def select_user_role_relation_by_user_name_and_role_id_list(self, user_name, role_id_list):    
        '''    
        查询用户角色关联表    
        :param user_name: 用户名称    
        :param role_id_list: 角色ID列表    
        :return: 用户ID列表    
        '''    
        sql = "SELECT user_id FROM user_info, user_role_relation WHERE user_info.user_name = %s AND user_info.user_id = user_role_relation.user_id AND user_role_relation.role_id IN %s"    
        params = (user_name, tuple(role_id_list))    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        user_id_list = []    
        for row in result:        
            user_id_list.append(row[0])    
        return user_id_list    
        
    
    def select_user_role_relation_by_role_name_and_user_id_list(self, role_name, user_id_list):    
        '''    
        查询用户角色关联表    
        :param role_name: 角色名称    
        :param user_id_list: 用户ID列表    
        :return: 用户ID列表    
        '''    
        sql = "SELECT user_id FROM user_role_relation, user_role WHERE user_role_relation.role_id = user_role.role_id AND user_role.role_name = %s AND user_role_relation.user_id IN %s"    
        params = (role_name, tuple(user_id_list))    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        user_id_list = []    
        for row in result:        
            user_id_list.append(row[0])    
        return user_id_list    
        
    
    def select_user_role_relation_by_user_id_list(self, user_id_list):    
        '''    
        查询用户角色关联表    
        :param user_id_list: 用户ID列表    
        :return: 角色ID列表    
        '''    
        sql = "SELECT role_id FROM user_role_relation WHERE user_id IN %s"    
        params = (tuple(user_id_list),)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        role_id_list = []    
        for row in result:        
            role_id_list.append(row[0])    
        return role_id_list    
        
    
    def select_user_role_relation_by_user_name_list(self, user_name_list):    
        '''    
        查询用户角色关联表    
        :param user_name_list: 用户名称列表    
        :return: 用户ID列表    
        '''    
        sql = "SELECT user_id FROM user_info, user_role_relation WHERE user_info.user_name IN %s AND user_info.user_id = user_role_relation.user_id"    
        params = (tuple(user_name_list),)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        user_id_list = []    
        for row in result:        
            user_id_list.append(row[0])    
        return user_id_list    
        
    
    def select_user_role_relation_by_role_name_list(self, role_name_list):    
        '''    
        查询用户角色关联表    
        :param role_name_list: 角色名称列表    
        :return: 用户ID列表    
        '''    
        sql = "SELECT user_id FROM user_role_relation, user_role WHERE user_role_relation.role_id = user_role.role_id AND user_role.role_name IN %s"    
        params = (tuple(role_name_list),)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        user_id_list = []    
        for row in result:        
            user_id_list.append(row[0])    
        return user_id_list    
        
    
    def select_user_role_relation_by_user_id_and_role_name_like(self, user_id, role_name_like):    
        '''    
        查询用户角色关联表    
        :param user_id: 用户ID    
        :param role_name_like: 角色名称模糊查询    
        :return: 角色ID列表    
        '''    
        sql = "SELECT role_id FROM user_role_relation, user_role WHERE user_role_relation.user_id = %s AND user_role_relation.role_id = user_role.role_id AND user_role.role_name LIKE %s"    
        params = (user_id, role_name_like)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        role_id_list = []    
        for row in result:        
            role_id_list.append(row[0])    
        return role_id_list    
        
    
    def select_user_role_relation_by_user_name_and_role_name_like(self, user_name, role_name_like):    
        '''    
        查询用户角色关联表    
        :param user_name: 用户名称    
        :param role_name_like: 角色名称模糊查询    
        :return: 用户ID列表    
        '''    
        sql = "SELECT user_id FROM user_info, user_role_relation, user_role WHERE user_info.user_name = %s AND user_info.user_id = user_role_relation.user_id AND user_role_relation.role_id = user_role.role_id AND user_role.role_name LIKE %s"    
        params = (user_name, role_name_like)    
        self.cursor.execute(sql, params)    
        result = self.cursor.fetchall()    
        user_id_list = []    
        for row in result:        
            user_id_list.append(row[0])    
        return user_id_list    